package AMS.model;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class StuTable {
	public void savenew(String stuid, String fbid, String url, String fbname) {
		Student stu = new Student();

		stu.setStuid(stuid);
		stu.setFbid(fbid);
		stu.setPoint(0);
		stu.setLevelno("1");
		stu.setEvaluation(0);
		stu.setKnowledge(0);
		stu.setAnswercount(0);
		stu.setLogindate(new Timestamp(System.currentTimeMillis()));
		stu.setQdate(new Timestamp(System.currentTimeMillis()));
		stu.setAdate(new Timestamp(System.currentTimeMillis()));
		stu.setPersonimg(url);
		stu.setFbname(fbname);
		stu.setEvacount(0);

		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();
		session.save(stu);
		tx.commit();
		session.close();
		HibernateUtil.shutdown();
	}

	public void setPoint(String stuid, int point) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set Point =? where StuId = ?");
		query.setInteger(0, point);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public int getPoint(String stuid) {
		int point = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			point = s.getPoint();
		}
		session.close();
		return point;
	}

	public void setImg(String stuid, String url) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set PersonImg =? where StuId = ?");
		query.setString(0, url);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public String getImg(String stuid) {
		String url = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			url = s.getPersonimg();
		}
		session.close();
		return url;
	}

	public void setFbName(String stuid, String fbname) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set FbName =? where StuId = ?");
		query.setString(0, fbname);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public String getFbName(String stuid) {
		String fbname = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			fbname = s.getFbname();
		}
		session.close();
		return fbname;
	}

	public ArrayList<String> getCourseName(String stuid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.classname from Elective as ele where ele.stuid = ? and ele.aid is not null and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public ArrayList<String> getCourseNo(String stuid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.classno from Elective as ele where ele.stuid = ? and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public ArrayList<String> getPlayCourseNo(String stuid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.classno from Elective as ele where ele.stuid = ? and ele.aid is not null and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public String getCourseNo(String stuid, String coursename) {
		String classno = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.classno from Elective as ele where ele.stuid = ? and ele.classname =? and ele.aid is not null and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, coursename);
		query.setString(2, "True");

		List result = query.list();

		for (Object aResult : result) {
			Elective e = (Elective) aResult;
			classno = e.getClassno();
		}
		session.close();
		return classno;
	}

	public ArrayList<String> getClassmate(String courseno) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.stuid from Elective as ele where ele.classno = ? and ele.semester = ?");

		query.setString(0, courseno);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public void setLevelNo(String stuid, String levelno) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set LevelNo =? where StuId = ?");
		query.setString(0, levelno);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public String getLevelNo(String stuid) {
		String levelno = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			levelno = s.getLevelno();
		}
		session.close();
		return levelno;
	}

	public String getLevelName(String levelno) {
		String levelname = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Level as lev where lev.levelno = ?");

		query.setString(0, levelno);

		List result = query.list();

		for (Object aResult : result) {
			Level l = (Level) aResult;
			levelname = l.getLevelname();
		}
		session.close();
		return levelname;
	}

	public String getAngel(String stuid, String classno) {
		String aid = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Elective as ele where ele.stuid = ? and ele.classno = ? and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, classno);
		query.setString(2, "True");

		List result = query.list();

		for (Object aResult : result) {
			Elective e = (Elective) aResult;
			aid = e.getAid();
		}
		session.close();
		return aid;
	}

	public String getMaster(String stuid, String classno) {
		String mid = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Elective as ele where ele.stuid = ? and ele.classno = ? and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, classno);
		query.setString(2, "True");

		List result = query.list();

		for (Object aResult : result) {
			Elective e = (Elective) aResult;
			mid = e.getMid();
		}
		session.close();
		return mid;
	}

	public int getCount(String stuid) {
		int count = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.classno from Elective as ele where ele.stuid = ? and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		count = al.size();
		return count;
	}

	public void setAngelMaster(String stuid, String classno, String aid,
			String mid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Elective set Aid =?,Mid =?,Evaluation =? where StuId = ? and ClassNo = ? and Semester = ?");
		query.setString(0, aid);
		query.setString(1, mid);
		query.setString(2, "False");
		query.setString(3, stuid);
		query.setString(4, classno);
		query.setString(5, "True");

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public void setEvaluation(String stuid, int evaluation) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set Evaluation =? where StuId = ?");
		query.setInteger(0, evaluation);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public ArrayList<String> getTopEvaluation() {
		Session session = HibernateUtil.getSessionFactory().openSession();

		String sql = "select StuId from StuTable order by Evaluation desc limit 0,5";

		SQLQuery sqlQuery = session.createSQLQuery(sql);

		List result = sqlQuery.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public int getEvaluation(String stuid) {
		int evaluation = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			evaluation = s.getEvaluation();
		}
		session.close();
		return evaluation;
	}

	public String getStuid(String fbid) {
		String stuid = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.fbid = ?");

		query.setString(0, fbid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			stuid = s.getStuid();
		}
		session.close();
		return stuid;
	}

	public void setFbid(String stuid, String fbid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set FbId =? where StuId = ?");
		query.setString(0, fbid);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public boolean getStuExist(String stuid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select stu.stuid from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}

		if (al.size() == 0)
			return false;
		else
			return true;

	}

	public ArrayList<String> getPlayClassmate(String courseno) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.stuid from Elective as ele where ele.classno = ? and ele.aid is null and ele.semester = ?");

		query.setString(0, courseno);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public int getClassmateCount(String courseno) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select ele.stuid from Elective as ele where ele.classno = ? and ele.semester = ?");

		query.setString(0, courseno);
		query.setString(1, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al.size();
	}

	public ArrayList<String> getCourseNo() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("select distinct ele.classno from Elective as ele where ele.semester = ?");

		query.setString(0, "True");

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList<String> al = new ArrayList<String>();

		while (it.hasNext()) {
			al.add((String) it.next());
		}
		return al;
	}

	public Timestamp getEva(String stuid, String classno) {
		Timestamp evaluation = null;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Elective as ele where ele.stuid = ? and ele.classno =? and ele.semester = ?");

		query.setString(0, stuid);
		query.setString(1, classno);
		query.setString(2, "True");

		List result = query.list();

		for (Object aResult : result) {
			Elective e = (Elective) aResult;
			evaluation = e.getEvaluation();
		}
		session.close();
		return evaluation;
	}

	public void setEva(String stuid, String classno) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Elective set Evaluation = CURRENT_TIMESTAMP where StuId = ? and Classno = ? and Semester = ?");

		query.setString(0, stuid);
		query.setString(1, classno);
		query.setString(2, "True");

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public void setLoginDate(String stuid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set LoginDate =? where StuId = ?");
		query.setTimestamp(0, new Timestamp(System.currentTimeMillis()));
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public String getLoginDate(String stuid) {
		Timestamp logindate = new Timestamp(System.currentTimeMillis());
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList al = new ArrayList();

		while (it.hasNext()) {
			al.add(it.next());
		}

		if (al.size() == 0)
			return "null";
		else {
			for (Object aResult : result) {
				Student s = (Student) aResult;
				logindate = s.getLogindate();
			}
			session.close();
			return logindate.toString();
		}
	}

	public void setQDate(String stuid, Timestamp qdate) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set QDate =? where StuId = ?");
		query.setTimestamp(0, qdate);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public String getQDate(String stuid) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		Timestamp qdate = new Timestamp(System.currentTimeMillis());

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList al = new ArrayList();

		while (it.hasNext()) {
			al.add(it.next());
		}

		if (al.size() == 0)
			return "null";
		else {

			for (Object aResult : result) {
				Student s = (Student) aResult;
				qdate = s.getQdate();
			}
			session.close();
			return qdate.toString();
		}
	}

	public void setADate(String stuid, Timestamp adate) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set ADate =? where StuId = ?");
		query.setTimestamp(0, adate);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public String getADate(String stuid) {
		Timestamp adate = new Timestamp(System.currentTimeMillis());
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		Iterator it = result.iterator();

		ArrayList al = new ArrayList();

		while (it.hasNext()) {
			al.add(it.next());
		}

		if (al.size() == 0)
			return "null";
		else {

			for (Object aResult : result) {
				Student s = (Student) aResult;
				adate = s.getAdate();
			}
			session.close();
			return adate.toString();
		}
	}

	public int getEvaCount(String stuid) {
		int evacount = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			evacount = s.getEvacount();
		}
		session.close();
		return evacount;
	}

	public void setEvaCount(String stuid, int evacount) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();

		Query query = session
				.createQuery("update Student set EvaCount =? where StuId = ?");
		query.setInteger(0, evacount);
		query.setString(1, stuid);

		query.executeUpdate();
		tx.commit();
		session.close();
	}

	public Long getFbId(String stuid) {
		String fbid = "";
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			fbid = s.getFbid();
		}
		session.close();
		return Long.parseLong(fbid);
	}

	public int getMail(String stuid) {
		int mail = 0;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session
				.createQuery("from Student as stu where stu.stuid = ?");

		query.setString(0, stuid);

		List result = query.list();

		for (Object aResult : result) {
			Student s = (Student) aResult;
			mail = s.getMail();
		}
		session.close();
		return mail;
	}
}